﻿using AppLibrary.WriteExcel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;

namespace ExportExcelMethods
{
    public class ExportExcelMethods
    {
        public static void ExportToExcel(DataTable data, int[] columnsWidth, string fileName, string sheetName = "sheet1")
        {
            HttpContext hc = HttpContext.Current;
            ArrayList al = new ArrayList();
            int count = data.Columns.Count;
            for (int i = 0; i < count; i++)
            {
                string t = data.Columns[i].Caption;
                al.Add(t);
            }

            AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();
            doc.FileName = hc.Server.UrlEncode(fileName) + ".xls";
            AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(sheetName);
            AppLibrary.WriteExcel.Cells cells = sheet.Cells;

            AppLibrary.WriteExcel.XF XFstyle = doc.NewXF();//添加样式的
            XFstyle.HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;
            XFstyle.Font.FontName = "宋体";//字体
            XFstyle.UseMisc = true;
            XFstyle.TextDirection = AppLibrary.WriteExcel.TextDirections.LeftToRight;//文本位置
            XFstyle.Font.Bold = false;//加粗
            #region 边框线的样式
            XFstyle.BottomLineStyle = 1;
            XFstyle.LeftLineStyle = 1;
            XFstyle.TopLineStyle = 1;
            XFstyle.RightLineStyle = 1;
            #endregion

            XFstyle.UseBorder = true;
            XFstyle.PatternBackgroundColor = AppLibrary.WriteExcel.Colors.Blue;
            XFstyle.PatternColor = AppLibrary.WriteExcel.Colors.White;
            XFstyle.Pattern = 1;

            #region 宽度
            if (columnsWidth != null)
            {
                for (int i = 0; i < columnsWidth.Length; i++)
                {
                    ColumnInfo colInfo = new ColumnInfo(doc, sheet);
                    colInfo.ColumnIndexStart = (ushort)i;
                    colInfo.ColumnIndexEnd = (ushort)i;
                    colInfo.Width = (ushort)(columnsWidth[i] * 256);
                    sheet.AddColumnInfo(colInfo);
                }
            }

            #endregion

            #region 添加表头
            int idx = 1;
            for (int i = 0; i < al.Count; i++)
            {
                cells.Add(1, idx, al[i], XFstyle);
                idx++;
            }
            #endregion

            int f = 2;//从第二行开始填充数据
            foreach (DataRow row in data.Rows)
            {
                for (int c = 0; c < data.Columns.Count; c++)
                {
                    cells.Add(f, c + 1, row[c] != DBNull.Value ? row[c] : null, XFstyle);
                }
                f++;
            }
            doc.Send();
            hc.Response.Flush();
            hc.Response.End();
        }
    }
}
